--用户
CREATE TABLE MYUSER (
  ID          INTEGER PRIMARY KEY,
  USERNAME    VARCHAR2(32),
  PASSWORD      VARCHAR2(32),
  NAME        VARCHAR2(32),
  MONEY       NUMBER(8, 2),
  CREATE_DATE DATE,
  LEVE       INTEGER,
  ISADMIN     INTEGER
);

--菜单
CREATE TABLE MENU (
  ID        INTEGER PRIMARY KEY,
  NAME      VARCHAR2(32),
  PRICE     NUMBER(8, 2),
  SHOP      VARCHAR2(32),
  STATE     INTEGER,
  SORT      INTEGER,
  CONDITION VARCHAR2(32)  -- 0123456：周日到周六（如：025表示周日、二、五展示）
);

--推荐
CREATE TABLE HOT (
  ID       INTEGER PRIMARY KEY,
  MENU_ID  INTEGER,
  POINT    INTEGER,
  EVALUATE VARCHAR2(32),
  AGREE    INTEGER,
  DISAGREE INTEGER
);

--订单
CREATE TABLE INDENT (
  ID          INTEGER PRIMARY KEY,
  USERID      INTEGER,
  CREATE_DATE DATE,
  MENUIDS     VARCHAR2(32),
  MEMO        VARCHAR2(32),
  FLAG        VARCHAR2(32)
);

CREATE TABLE DICTITEM (
  DICTID   VARCHAR2(32),
  DICTNAME VARCHAR2(32),
  GROUPID  VARCHAR2(32),
  SORT     INTEGER,
  STATE    VARCHAR2(32)
);

CREATE TABLE AUTH (
  ID     INTEGER PRIMARY KEY,
  KEY    VARCHAR2(32),
  NAME   VARCHAR2(32),
  MEMO   VARCHAR2(128),
  STATUS CHAR(1) --  1有效  0 无效
);

CREATE TABLE SHOP (
  ID    INTEGER PRIMARY KEY,
  NAME  VARCHAR2(32),
  SORT  INTEGER,
  STATE CHAR(1)
);


CREATE SEQUENCE seq_id
INCREMENT BY 1
START WITH 1
MAXVALUE 99999999
MINVALUE 1
NOCYCLE;


----------------------------
INSERT INTO myuser (id, username, password, name, money, create_date, leve, isAdmin)
VALUES (seq_id.nextval, 'admin', 'admin', '管理员', '0', sysdate, 9, 1);

-- 1：开，0：关
INSERT INTO auth (id, key, name, memo, status)
VALUES (seq_id.nextval,'eatThat', '退订', '退订按钮功能的开关', '1');
INSERT INTO auth (id,key, name, memo, status)
VALUES (seq_id.nextval,'closedTips', '订餐提示', '订餐提示的开关', '0');


INSERT INTO shop (id,name, sort, state) VALUES (seq_id.nextval,'广和原味', 1, 1);
INSERT INTO menu (id,name, price, shop, state, sort, condition)
VALUES (seq_id.nextval,'麻辣手撕鸡', 13, (select id from SHOP where name='广和原味'), 1, 1, '1235460');
INSERT INTO menu (id,name, price, shop, state, sort, condition)
VALUES (seq_id.nextval,'北京烤鸭', 20, (select id from SHOP where name='广和原味'), 2, 1, '1235460');
INSERT INTO menu (id,name, price, shop, state, sort, condition)
VALUES (seq_id.nextval,'把子肉', 12, (select id from SHOP where name='广和原味'), 3, 1, '1235460');


commit;